package com.ssyt.yt1.day24.preparedstatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.ssyt.yt1.common.Constant;

/**
 * 数据操作：增
 *
 * @author Hsu.
 * @since 2015年12月25日
 */
public class DMLDemo {

	/**
	 * 使用Statement完成新增：拼串很复杂！
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void testAddByStatement() throws ClassNotFoundException,
			SQLException {
		// 1、加载驱动
		Class.forName(Constant.DRIVER);

		// 2、连接数据库
		Connection connection = null;
		Statement statement = null;
		try {
			connection = DriverManager.getConnection(Constant.URL,
					Constant.USER, Constant.PASSWORD);
			// 3、操作数据
			statement = connection.createStatement();
			// 以下这些信息一般在实际开发中，是由外部传入
			String idcard = "500228188809096579";
			String username = "lisi";
			String password = "iloveyouzhangsan";
			double salary = 99999.99;
			String birthday = "2015-02-01";
			String memo = null;
			String sql = "insert into user values(null, '" + idcard + "', '"
					+ username + "', '" + password + "', " + salary + ", '"
					+ birthday + "', '" + memo + "')";
			int row = statement.executeUpdate(sql);
			System.out.println("受影响的行数：" + row);
		} finally {
			// 4、关闭连接
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
	}

	/**
	 * 使用Statement完成登录：会出现一个SQL注入的安全问题！
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static void testLoginByStatement() throws SQLException,
			ClassNotFoundException {
		// 登录的用户名和密码
		String username = "abc";
		String password = "' or true or '";
		// 1、加载驱动
		Class.forName(Constant.DRIVER);

		// 2、连接数据库
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = DriverManager.getConnection(Constant.URL,
					Constant.USER, Constant.PASSWORD);
			// 3、操作数据
			statement = connection.createStatement();
			String sql = "select * from user where username='" + username
					+ "' and password='" + password + "'";
			System.out.println(sql);
			resultSet = statement.executeQuery(sql);
			boolean flag = false;
			String loginedUsername = null;
			String loginedPassword = null;
			while (resultSet.next()) {
				loginedUsername = resultSet.getString("username");
				loginedPassword = resultSet.getString("password");
				flag = true;
			}

			if (flag) {
				System.out.println("登录成功!用户名：" + loginedUsername + ", 密码："
						+ loginedPassword);
			} else {
				System.out.println("登录失败！");
			}
		} finally {
			// 4、关闭连接
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
	}

	/**
	 * 使用PreparedStatement完成登录：以后的开发都使用这个，解决所有的statement会出现问题！
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static void testLoginByPrapredStatement() throws SQLException,
			ClassNotFoundException {
		// 登录的用户名和密码
		String username = "abc";
		String password = "' or true or '";
		// 1、加载驱动
		Class.forName(Constant.DRIVER);

		// 2、连接数据库
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DriverManager.getConnection(Constant.URL,
					Constant.USER, Constant.PASSWORD);
			// 3、操作数据
			String sql = "select * from user where username=? and password=?";
			preparedStatement = connection.prepareStatement(sql);
			// 给预编译语句添加具体的值
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, password);
			resultSet = preparedStatement.executeQuery();
			boolean flag = false;
			String loginedUsername = null;
			String loginedPassword = null;
			while (resultSet.next()) {
				loginedUsername = resultSet.getString("username");
				loginedPassword = resultSet.getString("password");
				flag = true;
			}

			if (flag) {
				System.out.println("登录成功!用户名：" + loginedUsername + ", 密码："
						+ loginedPassword);
			} else {
				System.out.println("登录失败！");
			}
		} finally {
			// 4、关闭连接
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		testLoginByPrapredStatement();
	}
}
